using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Windows.Forms;
namespace QLHXNK.DataAccess
{
    class BaoCaoData
    {
        public static DataTable getImportNoteReport(DateTime fromDate, DateTime toDate, int itemID)
        {
            try
            {
                String sqlstring = String.Empty;
                if (itemID != -1)
                    sqlstring = String.Format("        SELECT     im.import_note_date, im.import_note_code, i.item_code, i.item_name, ind.quantity, ind.net1, ind.net2, ind.selling_price, ind.profit1, ind.profit2 FROM         ((Import_Note im INNER JOIN           Import_Note_Detail ind ON im.ID = ind.import_note_id) INNER JOIN                     Item i ON ind.item_id = i.ID) WHERE  (i.ID = {0}) AND   (im.import_note_date >= #{1}#) AND (im.import_note_date <= #{2}#) ORDER BY im.import_note_date", itemID, fromDate.ToShortDateString(), toDate.ToShortDateString());
                else
                    sqlstring = String.Format("        SELECT     im.import_note_date, im.import_note_code, i.item_code, i.item_name, ind.quantity, ind.net1, ind.net2, ind.selling_price, ind.profit1, ind.profit2 FROM         ((Import_Note im INNER JOIN           Import_Note_Detail ind ON im.ID = ind.import_note_id) INNER JOIN                     Item i ON ind.item_id = i.ID) WHERE     (im.import_note_date >= #{0}#) AND (im.import_note_date <= #{1}#) ORDER BY im.import_note_date", fromDate.ToShortDateString(), toDate.ToShortDateString());

                ConnectionData connectionData = new ConnectionData();
                return connectionData.List(sqlstring);
            }
            catch
            {
                return null;
            }
        }

         public static DataTable getExpensesReport(String fromMonth, String fromYear,String toMonth, String toYear )
         {
             try
             {
                 
                 String sqlstring = String.Format("SELECT * FROM Monthly_Expenses WHERE (year_name = {1} AND year_name < {3} AND month_name >= {0}) OR (year_name > {1} AND year_name < {3}) OR (year_name > {1} AND year_name = {3} AND month_name <= {2}) OR (year_name = {1} AND year_name = {3} AND month_name >= {0} AND month_name <= {2})", fromMonth, fromYear, toMonth, toYear);
                 ConnectionData connectionData = new ConnectionData();
                 return connectionData.List(sqlstring);
             }
             catch
             {
                 return null;
             }
            
        }

        public static DataTable getItemByID(int ItemID)
        {
            try
            {
                String sqlstring = String.Format("SELECT * FROM Item WHERE ID = {0}", ItemID);
                
                ConnectionData connectionData = new ConnectionData();
                return connectionData.List(sqlstring);
            }
            catch
            {
                return null;
            }
        }
    }
}
